﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace 医院药库管理系统
{
    public partial class 库存清单 : Form
    {
        private DataTable csTable;
        private int PageSize;
        private int CurrentPageNo;
        private int MaxPageNo;
        private DataView CurrentPageView;

        public 库存清单()
        {
            InitializeComponent();
            
        }

        private void bt_qdfh_Click(object sender, EventArgs e)
        {
            首页 f3 = new 首页();
            f3.Show();
        }

        private void gb_gjc_Enter(object sender, EventArgs e)
        {

        }

        private void 库存清单_Load(object sender, EventArgs e)
        {
            this.PageSize =10;                                                                             //设置每页大小为10（行记录）；
            this.CurrentPageNo = 1;
           
            // TODO: 这行代码将数据加载到表“yyaoDataSet3.tb_Administrator”中。您可以根据需要移动或移除它。
            this.tb_AdministratorTableAdapter.Fill(this.yyaoDataSet3.tb_Administrator);
            // TODO: 这行代码将数据加载到表“yyaoDataSet2.tb_Medicine”中。您可以根据需要移动或移除它。
            this.tb_MedicineTableAdapter.Fill(this.yyaoDataSet2.tb_Medicine);
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = "SELECT * FROM tb_Medicine;";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            this.csTable = new DataTable();
            this.csTable.TableName = "kcqd";
            sqlConnection.Open();
            DataColumn rowIdColumn = new DataColumn();                                                    
            rowIdColumn.ColumnName = "RowID";                                                               //设置数据列的名称；
            rowIdColumn.DataType = typeof(int);                                                             //设置数据列的类型；类型需借助typeof获取；
            rowIdColumn.AutoIncrement = true;                                                               //设置数据列为自增列；
            rowIdColumn.AutoIncrementSeed = 1;                                                              //设置数据列的自增种子为1；
            rowIdColumn.AutoIncrementStep = 1;                                                              //设置数据列的自增步长为1；
            this.csTable.Columns.Add(rowIdColumn);                                                     
            sqlDataAdapter.Fill(this.csTable);
            

            sqlConnection.Close();                                                                          //关闭SQL连接；
            this.MaxPageNo = this.csTable.Rows.Count / this.PageSize + 1;                               //根据课程数据表的行集合的计数，计算最大页号；
            this.CurrentPageView = new DataView();                                                          //实例化本窗体的课程数据视图，用于筛选当前页的记录；
            this.CurrentPageView.Table = this.csTable;                                                  //设置课程数据视图对应的数据表；
            this.CurrentPageView.Sort = "RowID ASC";                                                        //设置课程数据视图的排序条件，即行编号；
            this.CurrentPageView.RowFilter =                                                                //设置课程数据视图的行筛选条件，即筛选当前页的记录；
                "RowID >" + (this.CurrentPageNo - 1) * this.PageSize
                + " AND RowID <=" + this.CurrentPageNo * this.PageSize;                                     //根据当前页号、每页大小，计算相应的行编号范围，并作为行筛选条件；
            this.dvg_kcqd.Columns.Clear();
            this.dvg_kcqd.DataSource = this.CurrentPageView;
            SqlCommand sqltype = new SqlCommand();
            sqltype.Connection = sqlConnection;
            sqltype.CommandText = "SELECT * FROM tb_Type;";
            SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();
            sqlDataAdapter1.SelectCommand = sqltype;
            DataTable Type = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter1.Fill(Type);
            sqlConnection.Close();


            DataGridViewComboBoxColumn typeColumn = new DataGridViewComboBoxColumn();                  //声明并实例化数据网格视图下拉框列，用于设置先修课程；
            typeColumn.Name = "TypeName";                                                             //设置下拉框列的名称；
            typeColumn.HeaderText = "药品类型";                                                        //设置下拉框列的表头文本；
            typeColumn.DataSource = Type;                                               //设置下拉框列的数据源为先修课程数据表；
            typeColumn.DisplayMember = "TypeName";                                                         //设置下拉框列的显示成员为（先修课程数据表的）名称（列）；
            typeColumn.ValueMember = "No";                                                             //设置下拉框列的值成员为（先修课程数据表的）编号（列）；
            typeColumn.DataPropertyName = "Type";                                               //设置下拉框列的数据属性名称为（课程数据表的）先修课程编号（列）；
            typeColumn.DisplayIndex = 11;
            typeColumn.ReadOnly = true;
            typeColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;                             //设置下拉框列的自动调整列宽模式为填充；
            this.dvg_kcqd.Columns.Add(typeColumn);

            SqlCommand sqlphy = new SqlCommand();
            sqlphy.Connection = sqlConnection;
            sqlphy.CommandText = "SELECT * FROM tb_Physicalstate;";
            SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter();
            sqlDataAdapter2.SelectCommand = sqlphy;
            DataTable Physicalstate = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter2.Fill(Physicalstate);
            sqlConnection.Close();


            DataGridViewComboBoxColumn PhysicalstateColumn = new DataGridViewComboBoxColumn();                  //声明并实例化数据网格视图下拉框列，用于设置先修课程；
            PhysicalstateColumn.Name = "PhysicalstateName";                                                             //设置下拉框列的名称；
            PhysicalstateColumn.HeaderText = "物理状态";                                                        //设置下拉框列的表头文本；
            PhysicalstateColumn.DataSource = Physicalstate;                                               //设置下拉框列的数据源为先修课程数据表；
            PhysicalstateColumn.DisplayMember = "PhysicalstateName";                                                         //设置下拉框列的显示成员为（先修课程数据表的）名称（列）；
            PhysicalstateColumn.ValueMember = "No";                                                             //设置下拉框列的值成员为（先修课程数据表的）编号（列）；
            PhysicalstateColumn.DataPropertyName = "Physicalstate";                                               //设置下拉框列的数据属性名称为（课程数据表的）先修课程编号（列）；
            PhysicalstateColumn.DisplayIndex = 13;
            PhysicalstateColumn.ReadOnly = true;
            PhysicalstateColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;                             //设置下拉框列的自动调整列宽模式为填充；
            this.dvg_kcqd.Columns.Add(PhysicalstateColumn);

            SqlCommand sqldosage = new SqlCommand();
            sqldosage.Connection = sqlConnection;
            sqldosage.CommandText = "SELECT * FROM tb_DosageForm;";
            SqlDataAdapter sqlDataAdapter3 = new SqlDataAdapter();
            sqlDataAdapter3.SelectCommand = sqldosage;
            DataTable DosageForm = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter3.Fill(DosageForm);
            sqlConnection.Close();


            DataGridViewComboBoxColumn DosageFormColumn = new DataGridViewComboBoxColumn();                  //声明并实例化数据网格视图下拉框列，用于设置先修课程；
            DosageFormColumn.Name = "PhysicalstateName";                                                             //设置下拉框列的名称；
            DosageFormColumn.HeaderText = "剂型";                                                        //设置下拉框列的表头文本；
            DosageFormColumn.DataSource = DosageForm;                                               //设置下拉框列的数据源为先修课程数据表；
            DosageFormColumn.DisplayMember = "Name";                                                         //设置下拉框列的显示成员为（先修课程数据表的）名称（列）；
            DosageFormColumn.ValueMember = "No";                                                             //设置下拉框列的值成员为（先修课程数据表的）编号（列）；
            DosageFormColumn.DataPropertyName = "DosageForm";
            DosageFormColumn.ReadOnly = true;
            DosageFormColumn.DisplayIndex = 15;                                                               //设置下拉框列的显示顺序；
            DosageFormColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;                             //设置下拉框列的自动调整列宽模式为填充；
            this.dvg_kcqd.Columns.Add(DosageFormColumn);
            int i = 0;
            int f = 0;
            while (i < this.dvg_kcqd.Rows.Count-1)
            {
                if (this.dvg_kcqd.Rows[i].Cells[10].Value.ToString().Length < 3)
                {
                    this.dvg_kcqd.Rows[i].Cells[10].Style.BackColor = Color.Red;
                    f = 1;
                }

                i++;
            }
            int j = 0;
            int m = 0;
            while (j  < this.dvg_kcqd.Rows.Count - 1)
            {
                if (Convert.ToDateTime(this.dvg_kcqd.Rows[j ].Cells[5].Value)<=DateTime.Now)
                {
                    this.dvg_kcqd.Rows[j].Cells[5].Style.BackColor = Color.Orange;
                    m = 1;
                }

                j++;
            }
            if (f == 1)
            {
                MessageBox.Show("红色预警库存量！");
            }
            if (m == 1)
            {
                MessageBox.Show("橙色过期预警！");
            }

            this.dvg_kcqd.Columns["Acronym"].Visible = false;
            this.dvg_kcqd.Columns["RowID"].Visible = false;
            this.dvg_kcqd.Columns["Type"].Visible = false;
            this.dvg_kcqd.Columns["PhysicalState"].Visible = false;
            this.dvg_kcqd.Columns["DosageForm"].Visible = false;
            this.dvg_kcqd.Columns["No"].HeaderText = "编号";
            this.dvg_kcqd.Columns["Name"].HeaderText = "名称";
            this.dvg_kcqd.Columns["Made_Date"].HeaderText = "生产日期";
            this.dvg_kcqd.Columns["Valid_Date"].HeaderText = "有效期至";
            this.dvg_kcqd.Columns["InDate"].HeaderText = "入库时间";
            this.dvg_kcqd.Columns["FirmName"].HeaderText = "生产厂商";
            this.dvg_kcqd.Columns["PurchasePrice"].HeaderText = "药品进价";
            this.dvg_kcqd.Columns["Price"].HeaderText = "零售价";
            this.dvg_kcqd.Columns["Type"].HeaderText = "药品类型";
            this.dvg_kcqd.Columns["PhysicalState"].HeaderText = "物理状态";
            this.dvg_kcqd.Columns["DosageForm"].HeaderText = "剂型";
            this.dvg_kcqd.Columns["in_principal"].HeaderText = "负责人";
            this.dvg_kcqd.Columns["Quantity"].HeaderText = "数量";
        }
      

        private void tb_szm_TextChanged_1(object sender, EventArgs e)
        {
          
            DataRow[] searchResultRows =
               this.csTable.Select("Acronym LIKE '%" + this.tb_szm.Text.Trim() + "%'");             //借助本窗体的课程数据表的方法Select，并提供与SQL类似的谓词表达式作为查询条件，根据拼音缩写进行模糊查询（仅支持%通配符）；查询将返回数据行数组；
            DataTable searchResultTable = this.csTable.Clone();                                     //借助本窗体的课程数据表的方法Clone，创建相同架构的空表，用于保存搜索结果所在数据行；
            foreach (DataRow row in searchResultRows)                                                       //遍历搜索结果所在数据行数组；
            {
                searchResultTable.ImportRow(row);                                                           //数据行导入数据表；
            }
            this.dvg_kcqd.DataSource = searchResultTable;
            this.dvg_kcqd.Columns["RowID"].Visible = false;
        }

        

        private void bt_ss_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;                             //在字符串变量中，描述连接字符串所需的服务器地址、数据库名称、集成安全性（即是否使用Windows验证）；
            SqlCommand sqlCommand = new SqlCommand();                                                       //声明并实例化SQL命令；
            sqlCommand.Connection = sqlConnection;
            
             if (cb_tdr.Text == "")
            {
                sqlCommand.CommandText = "SELECT * FROM tb_Medicine WHERE InDate =@InDate;";
                
                sqlCommand.Parameters.AddWithValue("@InDate",this.dtp_sj.Value);                                       
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataTable csTable1 = new DataTable();
                sqlConnection.Open();
                sqlDataAdapter.Fill(csTable1);
                sqlConnection.Close();
                this.dvg_kcqd.DataSource = csTable1; 
            }
            else
            {
                sqlCommand.CommandText = "SELECT * FROM tb_Medicine WHERE InDate = @InDate and in_principal LIKE '%'+@in_principal+'%';";                              //指定SQL命令的命令文本；
                sqlCommand.Parameters.AddWithValue("@InDate", this.dtp_sj.Value);
                sqlCommand.Parameters.AddWithValue("@in_principal", this.cb_tdr.Text.Trim()); 
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataTable csTable1 = new DataTable();
                sqlConnection.Open();
                sqlDataAdapter.Fill(csTable1);
                sqlConnection.Close();
                this.dvg_kcqd.DataSource = csTable1;
               
            } 

        }

        private void dvg_kcqd_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void bt_syy_Click(object sender, EventArgs e)
        {
            if (this.CurrentPageNo > 1)                                                                     //若当前页号大于1；
            {
                this.CurrentPageNo--;                                                                       //则当前页号递减；
            }
            this.CurrentPageView.RowFilter =                                                                //设置课程数据视图的行筛选条件，即筛选当前页的记录；
                "RowID >" + (this.CurrentPageNo - 1) * this.PageSize
                + " AND RowID <=" + this.CurrentPageNo * this.PageSize;
            this.lb_ym.Text = this.CurrentPageNo.ToString();
            this.lb_sum.Text = this.MaxPageNo.ToString();
            this.lb_sum.Text = "共" + this.MaxPageNo.ToString() + "页";
        }

        private void bt_next_Click(object sender, EventArgs e)
        {
            if (this.CurrentPageNo < this.MaxPageNo)                                                        //若当前页号尚未超出最大页号；
            {
                this.CurrentPageNo++;                                                                       //则当前页号递增；
            }
            this.CurrentPageView.RowFilter =                                                                //设置课程数据视图的行筛选条件，即筛选当前页的记录；
                "RowID >" + (this.CurrentPageNo - 1) * this.PageSize
                + " AND RowID <=" + this.CurrentPageNo * this.PageSize;
            this.lb_ym.Text = this.CurrentPageNo.ToString();
            this.lb_sum.Text ="共"+this.MaxPageNo.ToString()+"页"; 
        }

        private void lb_sum_Click(object sender, EventArgs e)
        {

        }

        private void dtp_sj_ValueChanged(object sender, EventArgs e)
        {

        }

      
    }
}
